﻿/*********************************************************************************************************************
VanguardDocumentation, Procedure SetTextProperty

© 2014 Erik Ackerholm

This file is part of SqlVanguard.

SqlVanguard is free software: you can redistribute it and/or modify it under the terms of the GNU General Public 
License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later 
version.

SqlVanguard is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied 
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with SqlVanguard. If not, see 
<http://www.gnu.org/licenses/>.
*********************************************************************************************************************/

if
	not exists (
		select *
		from sys.schemas as s
			inner join sys.objects as o on (s.schema_id = o.schema_id)
		where s.name = 'VanguardDocumentation' and o.[type] = 'P'
			and o.name = 'SetTextProperty'
	)
begin
	declare @SqlCommand as nvarchar(max);
	set @SqlCommand = '
		create procedure VanguardDocumentation.SetTextProperty
		as raiserror (''Procedure SetTextProperty is not implemented yet!'', 11, 1);
	';
	execute (@SqlCommand);
end;
GO

alter procedure VanguardDocumentation.SetTextProperty
	/*****************************************************************************************************************
	Stores a text as one or more extended properties for an entity.
	*****************************************************************************************************************/
	
	@EntityName                 nvarchar(512),
		/* Dot-separated name of entity where the text is stored. */
	@PropertyName               nvarchar(64),
		/* Name of property. If the text is longer than 3750 chars it will be split into chunks and an index is 
		added to the property name. */
	@NewText                    nvarchar(max),
		/* Text which is stored in properties. If the text is longer than 3750 chars it will be split into chunks of 
		this length. */
	@EntityType                 varchar(128)                = null
		/* Optionally give the type of entity where the text is stored. */
	
	with execute as owner
as
begin
	
	set nocount on;
	set xact_abort on;
	set lock_timeout 3000;
	set transaction isolation level read committed;
	
	declare @ErrorNo as integer;
	declare @ErrorMsg as nvarchar(1536);
	declare @ErrorLine as integer;
	declare @ErrorState as integer;
	
	declare @Level0Name as nvarchar(128);
	declare @Level1Name as nvarchar(128);
	declare @Level2Name as nvarchar(128);
	
	declare @Level0Type as varchar(64);
	declare @Level1Type as varchar(64);
	declare @Level2Type as varchar(64);
	
	declare @ExistingProperties as table (
		PropertyName        nvarchar(128) not null,
		primary key (PropertyName)
	);
	declare @PropertyToDrop as nvarchar(128);
	
	declare @ChunkStart as integer;
	declare @ChunkNo as smallint;
	declare @TextLength as integer;
	declare @IndexLength as tinyint;
	
	declare @ChunkName as nvarchar(128);
	declare @ChunkText as nvarchar(3750);
	
	begin try;
		
		begin transaction;
		
		-- Try to find entity.
		execute VanguardDocumentation.SelectEntity @EntityName, @EntityType,
			@Level0Name out, @Level0Type out, @Level1Name out, @Level1Type out, @Level2Name out, @Level2Type out;
		
		-- Remove any previous property.
		insert into @ExistingProperties
		select name
		from fn_listextendedproperty
			(null, @Level0Type, @Level0Name, @Level1Type, @Level1Name, @Level2Type, @Level2Name)
		where name = @PropertyName
			or name like (@PropertyName + '\_[0-9]%') escape '\'
			and name not like (@PropertyName + '\_[0-9]%[^0-9]%') escape '\';
		
		while
			exists (select * from @ExistingProperties)
		begin
			
			set @PropertyToDrop = (select top (1) PropertyName from @ExistingProperties);
			
			execute sp_dropextendedproperty @PropertyToDrop,
				@Level0Type, @Level0Name, @Level1Type, @Level1Name, @Level2Type, @Level2Name;
			
			delete from @ExistingProperties where PropertyName = @PropertyToDrop;
			
		end;
		
		-- Normalize line endings to Unix-style (LF, 10).
		set @NewText = replace(@NewText, char(13) + char(10), char(10));
		set @NewText = replace(@NewText, char(13), char(10));
		
		-- Initialize loop through text.
		set @ChunkStart = 1;
		set @ChunkNo = 0;
		set @TextLength = len(@NewText);
		-- max number of digits in index, so that the zero padding is the correct length
		set @IndexLength = len(rtrim( (@TextLength + 3749) / 3750 ));
		
		-- Loop through text and store it in chunks of 3750 chars (max length for nvarchar and extended properties).
		while
			@ChunkStart <= @TextLength
		begin
			
			set @ChunkName = @PropertyName
				+ coalesce('_' + right('0000000000' + nullif(rtrim(@ChunkNo), '0'), @IndexLength), '');
			set @ChunkText = substring(@NewText, @ChunkStart, 3750);
			
			execute sp_addextendedproperty @ChunkName, @ChunkText,
				@Level0Type, @Level0Name, @Level1Type, @Level1Name, @Level2Type, @Level2Name;
			
			set @ChunkStart += 3750;
			set @ChunkNo += 1;
			
		end;
		
		commit transaction;
		
	end try
	begin catch;
		
		set @ErrorNo = error_number();
		set @ErrorMsg = left(error_message(), 1536);
		set @ErrorLine = error_line();
		set @ErrorState = error_state();
		
		if @@trancount > 0
			rollback transaction;
		
		raiserror (740900, 16, @ErrorState, 'SetTextProperty', @ErrorNo, @ErrorLine, @ErrorMsg);
		
	end catch;
	
end;
GO




